﻿using AMAP.Domain;
using AMAP.ServiceClasses.Common;
using AMAP.ServiceInterfaces;
using AMAP.ServiceInterfaces.Common;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using ExternalOffer = AMAP.Areas.Admin.Models.Basket.AdminBasketCreateViewModel.ExternalOffer;

namespace AMAP.ServiceClasses
{
    public class OtherAmapService : BaseRepositoryService<Amap>, IOtherAmapService
    {
        private string TrollAmapConnectionString;
        private int TrollAmapId;

        public OtherAmapService(IDatabaseFactory databaseFactory)
            : base(databaseFactory)
        {
            this.TrollAmapConnectionString = ConfigurationManager.ConnectionStrings["TrollAMAPConnectionString"].ConnectionString;
            this.TrollAmapId = this.dataContext.AmapSet.FirstOrDefault(a => a.Name == "Troll Amap").Id;
        }

        public ExternalOffer GetById(int AmapId, int OfferId)
        {
            ExternalOffer Result;
            switch (this.dataContext.AmapSet.Find(AmapId).Name)
            {
                case "Troll Amap":
                    using (SqlConnection Connection = new SqlConnection(this.TrollAmapConnectionString))
                    {
                        DataSet ds = new DataSet();
                        string Request = "select o.ID_OFFRE as OfferId, o.QUANTITE_DISPONIBLE_OFFRE as Quantity, o.PRIX_UNITE_OFFRE as Price, p.UNITE_PRODUIT as Unit, p.TYPE_PRODUIT + ' (' + p.LABEL_PRODUIT + ')' as Name from TrollAMAP.dbo.OFFRE o inner join TrollAMAP.dbo.PRODUIT p on o.ID_PRODUIT = p.ID_PRODUIT where o.ID_OFFRE = " + OfferId;

                        Connection.Open();
                        (new SqlDataAdapter(new SqlCommand(Request, Connection))).Fill(ds);

                        DataRow Row = ds.Tables[0].Rows[0];
                        Result = new ExternalOffer
                        {
                            AmapId = this.TrollAmapId,
                            OfferId = int.Parse(Row["OfferId"].ToString()),
                            Name = Row["Name"].ToString(),
                            Price = double.Parse(Row["Price"].ToString()),
                            Unit = Row["Unit"].ToString(),
                            Quantity = double.Parse(Row["Quantity"].ToString())
                        };
                    }
                    break;
                default:
                    throw new Exception("This AmapId doesn't exists");
            }
            return Result;
        }

        public IList<ExternalOffer> GetLastValidOffers()
        {
            IList<ExternalOffer> Offers = new List<ExternalOffer>();

            using (SqlConnection Connection = new SqlConnection(this.TrollAmapConnectionString))
            {
                DataSet ds = new DataSet();
                string Request = "select o.ID_OFFRE as OfferId, o.QUANTITE_DISPONIBLE_OFFRE as Quantity, o.PRIX_UNITE_OFFRE as Price, p.UNITE_PRODUIT as Unit, p.TYPE_PRODUIT + ' (' + p.LABEL_PRODUIT + ')' as Name from TrollAMAP.dbo.OFFRE o inner join TrollAMAP.dbo.PRODUIT p on o.ID_PRODUIT = p.ID_PRODUIT where o.VENDREDI_DISPO_OFFRE = convert(date,'" + DateTime.Now.AddDays((int)DayOfWeek.Friday - ((int)DateTime.Now.DayOfWeek == 0 ? 7 : (int)DateTime.Now.DayOfWeek)).ToShortDateString() + "', 103)";

                Connection.Open();
                (new SqlDataAdapter(new SqlCommand(Request, Connection))).Fill(ds);

                foreach (DataRow Row in ds.Tables[0].Rows)
                {
                    Offers.Add(new ExternalOffer
                    {
                        AmapId = this.TrollAmapId,
                        OfferId = int.Parse(Row["OfferId"].ToString()),
                        Name = Row["Name"].ToString(),
                        Price = double.Parse(Row["Price"].ToString()),
                        Unit = Row["Unit"].ToString(),
                        Quantity = double.Parse(Row["Quantity"].ToString())
                    });
                }
            }

            return Offers;
        }

        public int OrderExternalProduct(int AmapId, int OfferId, double Quantity)
        {
            switch (this.dataContext.AmapSet.Find(AmapId).Name)
            {
                case "Troll Amap":
                    using (SqlConnection Connection = new SqlConnection(this.TrollAmapConnectionString))
                    {
                        Connection.Open();
                        new SqlCommand("INSERT INTO [dbo].[COMMANDE_AMAP]([ID_OFFRE],[ID_AMAP],[QUANTITE_COM_AMAP],[EST_CONFIRMEE_COM_AMAP]) VALUES(" + OfferId + ", 2, " + Quantity.ToString() + ", 1)", Connection).ExecuteNonQuery();

                        DataSet ds = new DataSet();
                        (new SqlDataAdapter(new SqlCommand("SELECT SCOPE_IDENTITY() as ExternalAmapOrderId;", Connection))).Fill(ds);
                        return int.Parse(ds.Tables[0].Rows[0]["ExternalAmapOrderId"].ToString());
                    }
                default:
                    throw new Exception("This AmapId doesn't exists");
            }
        }

        public void UpdateExternalOrder(IList<ExternalOrder> ExternalOrders)
        {
            foreach (int AmapId in ExternalOrders.Select(e => e.AmapId).Distinct())
            {
                IList<ExternalOrder> AmapExternalOrders = ExternalOrders.Where(e => e.AmapId == AmapId).ToList<ExternalOrder>();
                switch (this.dataContext.AmapSet.Find(AmapId).Name)
                {
                    case "Troll Amap":
                        using (SqlConnection Connection = new SqlConnection(this.TrollAmapConnectionString))
                        {
                            Connection.Open();

                            foreach (ExternalOrder ExternalOrder in AmapExternalOrders)
                            {
                                DataSet ds = new DataSet();
                                string Request = "select [QUANTITE_SATISFAITE_COM_AMAP] as ProvidedQuantity from TrollAMAP.dbo.COMMANDE_AMAP where ID_COM_AMAP = " + ExternalOrder.AmapExternalOrderId + " and [QUANTITE_SATISFAITE_COM_AMAP] >= 0";

                                (new SqlDataAdapter(new SqlCommand(Request, Connection))).Fill(ds);

                                foreach (DataRow Row in ds.Tables[0].Rows)
                                {
                                    ExternalOrder.ProvidedQuantity = double.Parse(Row["ProvidedQuantity"].ToString());
                                    new SqlCommand("UPDATE TrollAMAP.dbo.COMMANDE_AMAP set EST_CONFIRMEE_COM_AMAP = 1 where ID_COM_AMAP = " + ExternalOrder.AmapExternalOrderId, Connection).ExecuteNonQuery();
                                }
                            }
                        }
                        break;
                }
            }
        }
    }
}